﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{
    public static partial class DataManager
    {

        #region private variable

       

        #endregion

        #region private Method
        /// <summary>
        /// Check if Connection is Close , it will open it
        /// </summary>
        private static void OpenConnection()
        {
            if (_Connection.State != ConnectionState.Open)
            {
                _Connection.Open();
            }
        }
        /// <summary>
        /// Check if Connection is open , it will Close it
        /// </summary>
        private static void CloseConnection()
        {
            if (_Connection.State != ConnectionState.Closed)
            {
                _Connection.Close();
            }
        }

        #endregion

        #region Public Method

        public static List<TinyItem> GetListItemsLookUps(int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetListItemsLookUps";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();
                _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 9;
                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Items.Insert(0, x);
                return Items;
            }
            catch (Exception)
            { return null; }

        }
        public static List<TinyItem> GetListItemProjectEntity(int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetProjectEntities";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();
                
                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Items.Insert(0, x);
                return Items;
            }
            catch (Exception)
            { return null; }

        }
        public static List<TinyItem> GetListExecutingEntity(int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetContactEntities";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();

                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Items.Insert(0, x);
                return Items;
            }
            catch (Exception)
            { return null; }

        }
      
        public static List<TinyItem> SelectAllProjectCountries(int EntityId, int LanguageCode)
        {
            _Adapter.SelectCommand.CommandText = "SelectAllProjectCountries";
            _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            _Adapter.SelectCommand.Parameters.Clear();
            _Adapter.SelectCommand.Parameters.Add("@EntityId", SqlDbType.Int).Value = EntityId;
            _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
            _DataTable = new DataTable();
            _Adapter.Fill(_DataTable);

            List<TinyItem> projectCountry = new List<TinyItem>();

            for (int i = 0; i < _DataTable.Rows.Count; i++)
            {
                projectCountry.Add(new TinyItem());

                projectCountry[i].ID = Convert.ToInt32(_DataTable.Rows[i]["CountryId"]);
                projectCountry[i].Name = Convert.ToString(_DataTable.Rows[i]["CountryName"]);

            }

            return projectCountry;


        }
        /// <summary>
        /// select project according to country that you select it 
        /// </summary>
        /// <param name="LanguageCode"></param>
        /// <param name="CountryId"></param>
        /// <param name="EntityId"></param>
        /// <returns>Datatable of project by Country from Database</returns>
        public static DataTable SelectProjectsByCountry(int LanguageCode, int CountryId, int EntityId)
        {
            try
            {
                if (CountryId == 0)
                {

                    return SelectAllProject(LanguageCode, EntityId);
                }
                else
                {
                    _Adapter.SelectCommand.CommandText = "SelectProjectsByCountry";
                    _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    _Adapter.SelectCommand.Parameters.Clear();
                    _Adapter.SelectCommand.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;
                    _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
                    _Adapter.SelectCommand.Parameters.Add("@EntityId", SqlDbType.Int).Value = EntityId;
                    _DataTable = new DataTable();
                    _Adapter.Fill(_DataTable);

                    return _DataTable;
                }
            }
            catch (Exception e)
            {
                return _DataTable;
            }
        }

        /// <summary>
        /// select all project in Data base to use it in repeater
        /// </summary>
        /// <param name="LanguageCode"></param>
        /// <param name="CountryId"></param>
        /// <param name="EntityId"></param>
        /// <returns>Datatable of all project from Database</returns>
        public static DataTable SelectAllProject(int LanguageCode, int EntityId)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "SelectAllProject";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();

                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
                _Adapter.SelectCommand.Parameters.Add("@EntityId", SqlDbType.Int).Value = EntityId;
                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                return _DataTable;
            }
            catch (Exception e)
            {
                return _DataTable;
            }
        }
        public static DataTable SelectProjectById2(int ProjectId)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "SelectoneProject";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();

                _Adapter.SelectCommand.Parameters.Add("@ProjectId", SqlDbType.Int).Value = ProjectId;
                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                return _DataTable;
            }
            catch (Exception e)
            {
                return null;
            }
        }
       
        /// <summary>
        /// select project according to projectid ,languagecode 
        /// </summary>
        /// <param name="LanguageCode"></param>
        /// <param name="ProjectId"></param>
        /// <returns>Datatable of projectBYId </returns>
        public static DataTable SelectprojectById(int LanguageCode, int ProjectId,int Record)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "SelectProjectById";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();

                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
                
                
                if (ProjectId != 0 && Record != 0)
                { _Adapter.SelectCommand.Parameters.Add("@ProjectId", SqlDbType.Int).Value = ProjectId; }
                else 
               if ( Record != 0)
                {
                    ProjectId = Record;
                  _Adapter.SelectCommand.Parameters.Add("@ProjectId", SqlDbType.Int).Value = ProjectId; 
                }
              else
                  _Adapter.SelectCommand.Parameters.Add("@ProjectId", SqlDbType.Int).Value = ProjectId;
              
               
                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                if (_DataTable.Rows.Count == 0 && ProjectId==0 )
                { return null; }
                else
                    if (_DataTable.Rows.Count == 0)
                    {
                        _DataTable.Rows.Add(_DataTable.NewRow());
                  
                    }


                if (_DataTable.Rows[0]["ExecutingAgencyId"] == DBNull.Value) _DataTable.Rows[0]["ExecutingAgencyId"] = 0;
                if (_DataTable.Rows[0]["IsElectrical"] == DBNull.Value) _DataTable.Rows[0]["IsElectrical"] = false;
                if (_DataTable.Rows[0]["IsThermal"] == DBNull.Value) _DataTable.Rows[0]["IsThermal"] = false;
                if (_DataTable.Rows[0]["IsImplemented"] == DBNull.Value) _DataTable.Rows[0]["IsImplemented"] = false;
                if (_DataTable.Rows[0]["ProjectId"] == DBNull.Value) _DataTable.Rows[0]["ProjectId"] = ProjectId;
                if (_DataTable.Rows[0]["SectorId"] == DBNull.Value) _DataTable.Rows[0]["SectorId"] = 0;
                if (_DataTable.Rows[0]["StatusId"] == DBNull.Value) _DataTable.Rows[0]["StatusId"] = 0;
             //   if (_DataTable.Rows[0]["PickupsInstalledArea"] == DBNull.Value) _DataTable.Rows[0][" PickupsInstalledArea"] = 0; 
                if (_DataTable.Rows[0]["ProjectGroupId"] == DBNull.Value) _DataTable.Rows[0]["ProjectGroupId"] = 0;
                if (_DataTable.Rows[0]["TypeId"] == DBNull.Value) _DataTable.Rows[0]["TypeId"] = 0;
                if (_DataTable.Rows[0]["CountryId"] == DBNull.Value) _DataTable.Rows[0]["CountryId"] = 0;
 
                return _DataTable;
            }
            catch (Exception e)
            {
                return _DataTable;
            }
        }


        /// <summary>
        /// insert one project into Database its take paramter to transfer storedprocedure
        /// </summary>
        /// <param name="project"></param>
        /// <returns>return roweffect in Database to check if the operation is done or not</returns>
        public static int InsertProject(Project project)
        {
            try
            {
                _Command.CommandText = "InsertProject";
                _Command.CommandType = CommandType.StoredProcedure;

                _Command.Parameters.Clear();

                //set paramters with value 
                //_Command.Parameters.Add("@ProjectId", SqlDbType.Int).Value = project.ProjectId;
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = project.CountryId;
                _Command.Parameters.Add("@TypeId", SqlDbType.Int).Value = project.TypeId;
                _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = project.LanguageCode;
                _Command.Parameters.Add("@ProjectGroupId", SqlDbType.Int).Value = project.ProjectGroupId;
                _Command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = project.Name;
                _Command.Parameters.Add("@Place", SqlDbType.NVarChar).Value = project.Place;
                _Command.Parameters.Add("@EntityId", SqlDbType.Int).Value = project.EntityId;
                _Command.Parameters.Add("@Description", SqlDbType.NVarChar).Value = project.Description;
              
                _Command.Parameters.Add("@PickupsInstalledArea ", SqlDbType.NVarChar).Value = project.PickupsInstalledArea;
             
                _Command.Parameters.Add("@ConstructionCompanies", SqlDbType.NVarChar).Value = project.ConstructionCompanies;
          
                // every one check if ==0 Enter with DBNULL.value else enter value of project 

                if (project.OperationYear != 0)
                    _Command.Parameters.Add("@OperationYear", SqlDbType.Int).Value = project.OperationYear;
                else _Command.Parameters.Add("@OperationYear", SqlDbType.Int).Value = DBNull.Value;
            
                if(project.InstalledCapacity!=0)
                _Command.Parameters.Add("@InstalledCapacity ", SqlDbType.Float).Value = project.InstalledCapacity;
                else _Command.Parameters.Add("@InstalledCapacity ", SqlDbType.Float).Value =DBNull.Value;
               
                if(project.EnergyProducedPerYearRate!=0)
                    _Command.Parameters.Add("@EnergyProducedPerYearRate ", SqlDbType.Float).Value = project.EnergyProducedPerYearRate;
                else  _Command.Parameters.Add("@EnergyProducedPerYearRate ", SqlDbType.Float).Value =DBNull.Value;
             
                if (project.ExecutingAgencyId != 0)
                    _Command.Parameters.Add("@ExecutingAgencyId", SqlDbType.Int).Value = project.ExecutingAgencyId;
                else _Command.Parameters.Add("@ExecutingAgencyId", SqlDbType.Int).Value = DBNull.Value;

                if (project.SectorId != 0)
                    _Command.Parameters.Add("@SectorId", SqlDbType.Int).Value = project.SectorId;
                else _Command.Parameters.Add("@SectorId", SqlDbType.Int).Value = DBNull.Value;

                if (project.StatusId != 0)
                    _Command.Parameters.Add("@StatusId", SqlDbType.Int).Value = project.StatusId;
                else _Command.Parameters.Add("@StatusId", SqlDbType.Int).Value = DBNull.Value;
                if (project.IsElectrical != null)
                    _Command.Parameters.Add("@IsElectrical", SqlDbType.Bit).Value = project.IsElectrical;
                else
                    _Command.Parameters.Add("@IsElectrical", SqlDbType.Bit).Value = false;
                if (project.IsThermal != null)
                    _Command.Parameters.Add("@IsThermal", SqlDbType.Bit).Value = project.IsThermal;
                else
                    _Command.Parameters.Add("@IsThermal", SqlDbType.Bit).Value = false;
                if (project.IsImplemented != null)
                    _Command.Parameters.Add("@IsImplemented", SqlDbType.Bit).Value = project.IsImplemented;
                else
                    _Command.Parameters.Add("@IsImplemented", SqlDbType.Bit).Value = false;
                OpenConnection();
                int roweffect = Convert.ToInt32(_Command.ExecuteNonQuery());
                CloseConnection();
                return roweffect;
            }
            catch (Exception e)
            {
                return 0;
            }
           
        }
        /// <summary>
        /// Update project in Database ... takes paramter to storedprocedure
        /// </summary>
        /// <param name="project"></param>
        /// <returns>return roweffect in Database to check if the operation is done or not</returns>

        public static int UpdateProject(Project project)
        {
            try
            {//calling procedure 
                _Command.CommandText = "UpdateProject";
                _Command.CommandType = CommandType.StoredProcedure;

                _Command.Parameters.Clear();
                //Set Paramter of stored procedure
                _Command.Parameters.Add("@ProjectId", SqlDbType.Int).Value = project.ProjectId;
                _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = project.LanguageCode;
                _Command.Parameters.Add("@ProjectGroupId", SqlDbType.Int).Value = project.ProjectGroupId;
                _Command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = project.Name;
                _Command.Parameters.Add("@Place", SqlDbType.NVarChar).Value = project.Place;
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = project.CountryId;
                _Command.Parameters.Add("@TypeId", SqlDbType.Int).Value = project.TypeId;
                _Command.Parameters.Add("@Description", SqlDbType.NVarChar).Value = project.Description;
               _Command.Parameters.Add("@PickupsInstalledArea ", SqlDbType.NVarChar).Value = project.PickupsInstalledArea;
                 _Command.Parameters.Add("@ConstructionCompanies", SqlDbType.NVarChar).Value = project.ConstructionCompanies;
          
                //Check if value !=0  set value with paramter else set it with DBNULL
                if (project.OperationYear != 0)
                    _Command.Parameters.Add("@OperationYear", SqlDbType.Int).Value = project.OperationYear;
                else _Command.Parameters.Add("@OperationYear", SqlDbType.Int).Value = DBNull.Value;
              
                if (project.InstalledCapacity != 0)
                    _Command.Parameters.Add("@InstalledCapacity ", SqlDbType.Float).Value = project.InstalledCapacity;
                else _Command.Parameters.Add("@InstalledCapacity ", SqlDbType.Float).Value = DBNull.Value;

                if (project.EnergyProducedPerYearRate != 0)
                    _Command.Parameters.Add("@EnergyProducedPerYearRate ", SqlDbType.Float).Value = project.EnergyProducedPerYearRate;
                else _Command.Parameters.Add("@EnergyProducedPerYearRate ", SqlDbType.Float).Value = DBNull.Value;

                if (project.ExecutingAgencyId != 0)
                    _Command.Parameters.Add("@ExecutingAgencyId", SqlDbType.Int).Value = project.ExecutingAgencyId;
                else _Command.Parameters.Add("@ExecutingAgencyId", SqlDbType.Int).Value = DBNull.Value;

                if (project.SectorId != 0)
                    _Command.Parameters.Add("@SectorId", SqlDbType.Int).Value = project.SectorId;
                else _Command.Parameters.Add("@SectorId", SqlDbType.Int).Value = DBNull.Value;

                if (project.StatusId != 0)
                    _Command.Parameters.Add("@StatusId", SqlDbType.Int).Value = project.StatusId;
                else _Command.Parameters.Add("@StatusId", SqlDbType.Int).Value = DBNull.Value;

                if (project.IsElectrical != null)
                    _Command.Parameters.Add("@IsElectrical", SqlDbType.Bit).Value = project.IsElectrical;
                else
                    _Command.Parameters.Add("@IsElectrical", SqlDbType.Bit).Value = false;
                if (project.IsThermal != null)
                    _Command.Parameters.Add("@IsThermal", SqlDbType.Bit).Value = project.IsThermal;
                else
                    _Command.Parameters.Add("@IsThermal", SqlDbType.Bit).Value = false;
                if (project.IsImplemented != null)
                    _Command.Parameters.Add("@IsImplemented", SqlDbType.Bit).Value = project.IsImplemented;
                else
                    _Command.Parameters.Add("@IsImplemented", SqlDbType.Bit).Value = false;

                OpenConnection();
                int roweffect = Convert.ToInt32(_Command.ExecuteNonQuery());
                CloseConnection();
                return roweffect;
            }
            catch (Exception e)
            { return 0; }
        }
        /// <summary>
        /// Delete one project according to project Id , languageCode 
        /// </summary>
        /// <param name="ProjectId"></param>
        /// <param name="LanguageCode"></param>
        /// <returns>return roweffect in Database to check if the operation is done or not</returns>

        public static int DeleteProject(int ProjectId, int LanguageCode)
        {
            try
            {
                _Command.CommandText = "DeleteProject";
                _Command.CommandType = CommandType.StoredProcedure;

                _Command.Parameters.Clear();

                _Command.Parameters.Add("@ProjectId", SqlDbType.Int).Value = ProjectId;
                _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;


                OpenConnection();
                int roweffect = Convert.ToInt32(_Command.ExecuteNonQuery());
                CloseConnection();
                return roweffect;
            }
            catch (Exception e)
            {
                return 0;
            }
        }
        /// <summary>
/// to Get all entity in Database 
/// </summary>
/// <param name="LanguageCode">language Code from session </param>
/// <returns>list of all entities ID,Name</returns>
        public static List<TinyItem> GetEntity(int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetItems";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();
                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);
                // create new list 
                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Items.Insert(0, x);
                return Items;
            }
            catch (Exception e)
            {
                return null;
            }
        
        }

        //public static List<TinyItem> GetALLCountries(int LanguageCode)
        //{
        //    try
        //    {
        //        _Adapter.SelectCommand.CommandText = "GetCountries";
        //        _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        //        _Adapter.SelectCommand.Parameters.Clear();
        //        _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
        //        _DataTable = new DataTable();
        //        _Adapter.Fill(_DataTable);

        //        // create new list 
        //        List<TinyItem> Countries = new List<TinyItem>();

        //        for (int i = 0; i < _DataTable.Rows.Count; i++)
        //        {
        //            Countries.Add(new TinyItem());

        //            Countries[i].ID = Convert.ToInt32(_DataTable.Rows[i]["CountryId"]);
        //            Countries[i].Name = Convert.ToString(_DataTable.Rows[i]["CountryName"]);

        //        }

        //        return Countries;
        //    }
        //    catch (Exception e)
        //    { return null; }
        //}
        /// <summary>
        /// Get All Countries 
        /// </summary>
        /// <param name="LanguageCode">language Code from session</param>
        /// <returns>list of counties ID,Name</returns>
        public static List<TinyItem> GetCountries(int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetCountries";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();
                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                // create new list 
                List<TinyItem> Countries = new List<TinyItem>();
             
                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Countries.Add(new TinyItem());

                    Countries[i].ID = Convert.ToInt32(_DataTable.Rows[i]["CountryId"]);
                    Countries[i].Name = Convert.ToString(_DataTable.Rows[i]["CountryName"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Countries.Insert(0, x);
                return Countries;
            }
            catch (Exception e)
            { return null; }
        }
        /// <summary>
        /// Get Contacts according to CountryId w EntityId
        /// </summary>
        /// <param name="LanguageCode"></param>
        /// <param name="CountryId"></param>
        /// <param name="EntityId"></param>
        /// <returns></returns>
        public static List<TinyItem> GetContacts(int LanguageCode,int CountryId,int EntityId)
        {

            try
            {
                _Adapter.SelectCommand.CommandText = "GetContact";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;


                _Adapter.SelectCommand.Parameters.Clear();

                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;
                _Adapter.SelectCommand.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;
                _Adapter.SelectCommand.Parameters.Add("@EntityId", SqlDbType.Int).Value = EntityId;
                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);
                // create new list 
                List<TinyItem> Contacts = new List<TinyItem>();

                Contacts.Add(new TinyItem());

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Contacts.Add(new TinyItem());

                    Contacts[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ContactId"]);
                    Contacts[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }

                return Contacts;
            }
            catch (Exception e) { return null; }
        }
      //  ------------------- To list Project 


        public static List<TinyItem> GetLanguageCode()
        {
            try
            {
                List<TinyItem> Items = new List<TinyItem>();
               Items.Add(new TinyItem(0,""));
                Items.Add(new TinyItem(1033,"English"));
                Items.Add(new TinyItem(1036, "France"));
                Items.Add(new TinyItem(1025, "Arabic"));
                
                return Items;
            }
            catch (Exception e)
            {
                return null;
            }
        
        }

        public static List<TinyItem> GetListItemProjectTypes(int ParentId, int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetListProjectTypesLookUps";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();

                _Adapter.SelectCommand.Parameters.Add("@CategoryId", SqlDbType.Int).Value = 2;
                _Adapter.SelectCommand.Parameters.Add("@ParentId", SqlDbType.Int).Value = ParentId;
                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
               Items.Insert(0, x);
                return Items;
            }
            catch (Exception e)
            {
                return null;
            }
        }



        public static List<TinyItem> GetListProjectSector(int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetProjectSector";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();

                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Items.Insert(0, x);
                return Items;
            }
            catch (Exception e)
            {
                return null;
            }
        }

        public static List<TinyItem> GetListProjectStatus(int LanguageCode)
        {
            try
            {
                _Adapter.SelectCommand.CommandText = "GetProjectStatus";
                _Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _Adapter.SelectCommand.Parameters.Clear();

                _Adapter.SelectCommand.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

                _DataTable = new DataTable();
                _Adapter.Fill(_DataTable);

                List<TinyItem> Items = new List<TinyItem>();

                for (int i = 0; i < _DataTable.Rows.Count; i++)
                {
                    Items.Add(new TinyItem());

                    Items[i].ID = Convert.ToInt32(_DataTable.Rows[i]["ItemId"]);
                    Items[i].Name = Convert.ToString(_DataTable.Rows[i]["Name"]);

                }
                TinyItem x = new TinyItem();
                x.ID = 0;
                x.Name = "";
                Items.Insert(0, x);
                return Items;
            }
            catch (Exception e)
            {
                return null;
            }
        }
            #endregion
    }
}
